作者：yinghccll | 来源：互联网 | 2023-06-03 13:29
作者&＃xff1a;蒋步星
来源&＃xff1a;数据蒋堂
本文共1200字&＃xff0c;建议阅读9分钟。在数据查询时&＃xff0c;有时会碰到数据量很大的清单报表。
在数据查询时&＃xff0c;有时会碰到数据量很大的清单报表。用户输入的查询条件很宽泛&＃xff0c;可能会从数据库中查出几百上千万行甚至过亿的记录。如果等着把这些记录全部检索出来再生成报表呈现&＃xff0c;那需要很长时间&＃xff0c;用户体验恶劣&＃xff1b;而且报表一般采用内存运算机制&＃xff0c;大多数情况下也装不下这么多数据。所以&＃xff0c;我们一般都是使用分页呈现的方式&＃xff0c;尽量快速地呈现出第一页&＃xff0c;然后可以随意翻页显示&＃xff0c;每次只显示一页&＃xff0c;也不会造成内存溢出。
那么&＃xff0c;一般的报表工具或BI系统都是怎么实现这一机制的呢&＃xff1f;
绝大多数产品都是使用数据库分页的方法来做的。
具体来讲&＃xff0c;就是利用数据库提供的返回指定行号范围内记录的语法。界面端根据当前页号计算出行号范围&＃xff08;每页显示固定行数&＃xff09;作为参数拼入SQL中&＃xff0c;数据库就会只返回当前页的记录&＃xff0c;从而实现分页呈现的效果。
这样做&＃xff0c;会有两个问题&＃xff1a;
1. 翻页时效率较差
用这种办法呈现出第一页来一般都会比较快&＃xff0c;但如果向后翻页时&＃xff0c;这个原始取数的SQL会被再次执行&＃xff0c;并且将前面页涉及的记录跳过。有些数据库没有OFFSET关键字&＃xff0c;就只能由界面端自行跳过这些数据&＃xff08;取出后丢弃&＃xff09;&＃xff0c;像ORACLE还需要用子查询产生一个序号才能再用序号做过滤&＃xff0c;这些动作都会浪费时间&＃xff0c;前几页还感觉不明显&＃xff0c;但如果翻到的页号比较大时&＃xff0c;就会有等待感了。
2. 可能出现数据不一致
一般来说&＃xff0c;每次按页取数时发出的SQL是独立的。这样&＃xff0c;如果在两页取数之间数据库又有了插入删除动作&＃xff0c;这时取出来的数据将是最新的&＃xff0c;很可能和原来的页号匹配不上了。比如第1页取出20行记录后&＃xff0c;在取第2页前&＃xff0c;第1页的20行记录中被删除了1行&＃xff0c;那么这时候取出来的第2页的第1行就会是原来的第22行记录&＃xff0c;原来的第21行会落到第1页去了&＃xff0c;要再倒翻页才能看到。如果基于这些数据做汇总统计&＃xff0c;那会出现错误的结果。
还有一种不常用的方法。向数据库发出取数SQL生成游标&＃xff0c;从中取出一页后呈现&＃xff0c;但并不终止这个游标&＃xff0c;要取下一页的时候再继续取数。这种方法能克服上述两个问题&＃xff0c;不会发生不一致的现象&＃xff0c;但绝大多数的数据库游标只能向后取数而不是倒回去&＃xff0c;这样在界面上的表现就是只能向后翻页了&＃xff0c;这一点很难向业务用户解释&＃xff0c;所以很少用这种办法。
也可以是两种办法的结合&＃xff0c;向后翻页时用后一种办法&＃xff0c;一旦发生向前翻页时&＃xff0c;则重新执行取数SQL。这样比每次分页取数的体验略好一些&＃xff0c;但并没有根本上解决问题。
还有什么好办法呢&＃xff1f;
把取数和呈现做成两个异步线程&＃xff0c;取数线程发出SQL后就不断取出数据并缓存到本地存储中&＃xff0c;呈现线程根据页数计算出行数到本地缓存中去获取数据显示。这样&＃xff0c;只要已经取过的数据就能快速呈现&＃xff0c;不会有等待感&＃xff0c;还没取到的数据需要等待一下也是正常可理解的&＃xff1b;而取数线程只涉及一句SQL&＃xff0c;在数据库中是同一个事务&＃xff0c;也不会有不一致的问题。这样&＃xff0c;两个问题都能得到解决。不过这需要设计一种可以按行号随机访问记录的存储格式&＃xff0c;不然要靠遍历把记录数出来&＃xff0c;那反应仍然会很迟钝。
在当前数据库系统不直接支持这种机制时&＃xff0c;只能是报表工具或BI系统受累自己写这些程序了&＃xff0c;对于有大清单报表呈现需求的用户&＃xff0c;就要认真考察这些功能点了。
润乾软件创始人、首席科学家
清华大学计算机硕士&＃xff0c;中国大数据产业生态联盟专家委员&＃xff0c;著有《非线性报表模型原理》等&＃xff0c;1989年&＃xff0c;中国首个国际奥林匹克数学竞赛团体冠军成员&＃xff0c;个人金牌&＃xff1b;2000年&＃xff0c;创立润乾公司&＃xff1b;2004年&＃xff0c;首次在润乾报表中提出非线性报表模型&＃xff0c;完美解决了中国式复杂报表制表难题&＃xff0c;目前该模型已经成为报表行业的标准&＃xff1b;2014年&＃xff0c;经过7年开发&＃xff0c;润乾软件发布不依赖关系代数模型的计算引擎——集算器&＃xff0c;有效地提高了复杂结构化大数据计算的开发和运算效率&＃xff1b;2015年&＃xff0c;润乾软件被福布斯中文网站评为“2015福布斯中国非上市潜力企业100强”&＃xff1b;2016、2017年&＃xff0c;荣获中国电子信息产业发展研究院评选的“中国软件和信息服务业十大领军人物”&＃xff1b;2017年度中国数据大工匠、数据领域专业技术讲堂《数据蒋堂》创办者。
《数据蒋堂》的作者蒋步星&＃xff0c;从事信息系统建设和数据处理长达20多年的时间。他丰富的工程经验与深厚的理论功底相互融合、创新思想与传统观念的相互碰撞&＃xff0c;虚拟与现实的相互交织&＃xff0c;产生出了一篇篇的沥血之作。此连载的内容涉及从数据呈现、采集到加工计算再到存储以及挖掘等各个方面。大可观数据世界之远景、小可看技术疑难之细节。针对数据领域一些技术难点&＃xff0c;站在研发人员的角度从浅入深&＃xff0c;进行全方位、360度无死角深度剖析&＃xff1b;对于一些业内观点&＃xff0c;站在技术人员角度阐述自己的思考和理解。蒋步星还会对大数据的发展&＃xff0c;站在业内专家角度给予预测和推断。静下心来认真研读你会发现&＃xff0c;《数据蒋堂》的文章&＃xff0c;有的会让用户避免重复前人走过的弯路&＃xff0c;有的会让攻城狮面对扎心的难题茅塞顿开&＃xff0c;有的会为初入行业的读者提供一把开启数据世界的钥匙&＃xff0c;有的甚至会让业内专家大跌眼镜&＃xff0c;产生思想交锋。
京公网安备 11010802041100号